﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace SIS.DataAccess
{
	/// <summary>
	/// Author: hieunv
	/// </summary>
	[Serializable]
	class Section : System.Data.DataTable
	{
        public const string COURSE_ID = "course_id";
		public const string SECTION_ID = "sec_id";
		public const string SEMESTER = "semester";
		public const string YEAR = "year";
		public const string BUILDING = "building";
		public const string ROOM_NUMBER = "room_number";
		public const string TIME_SLOT_ID = "time_slot_id";
		/// <summary>
		/// 
		/// </summary>
		public Section()
			: base("Section")
		{
			var courseID = new DataColumn(COURSE_ID, typeof(string));
			courseID.AllowDBNull = false;

			var sectionID = new DataColumn(SECTION_ID, typeof(string));
			sectionID.AllowDBNull = false;

			var semester = new DataColumn(SEMESTER, typeof(string));
			semester.AllowDBNull = false;

			var year = new DataColumn(YEAR, typeof(int));
			year.AllowDBNull = false;

			var building = new DataColumn(BUILDING, typeof(string));
			building.AllowDBNull = true;

			var roomNumber = new DataColumn(ROOM_NUMBER, typeof(string));
			building.AllowDBNull = true;

			var timeSlotID = new DataColumn(TIME_SLOT_ID, typeof(string));
			timeSlotID.AllowDBNull = true;

			this.Columns.Add(courseID);
			this.Columns.Add(sectionID);
			this.Columns.Add(semester);
			this.Columns.Add(year);
			this.Columns.Add(building);
			this.Columns.Add(roomNumber);
			this.Columns.Add(timeSlotID);

			this.PrimaryKey = new DataColumn[] { courseID, sectionID, semester, year};
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="info"></param>
		/// <param name="context"></param>
		public Section(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context)
			: base(info, context)
		{

		}
		/// <summary>
		/// 
		/// </summary>
		public void Fill()
		{
			DataServices.ExecuteQuery(QueryType.SELECT, CommandType.Text, "SELECT * FROM section", null, this);
		}		
		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		protected override Type GetRowType()
		{
			return typeof(Business.Section);
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="builder"></param>
		/// <returns></returns>
		protected override DataRow NewRowFromBuilder(DataRowBuilder builder)
		{
			return new Business.Section(builder);
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="section"></param>
		public bool Insert(Business.Section section)
		{
			try
			{
				this.Rows.Add(section);
				List<SqlParameter> parameters = new List<SqlParameter>();
				parameters.Add(new SqlParameter("@p1", SqlDbType.VarChar, 8, COURSE_ID));
				parameters.Add(new SqlParameter("@p2", SqlDbType.VarChar, 8, SECTION_ID));
				parameters.Add(new SqlParameter("@p3", SqlDbType.VarChar, 6, SEMESTER));
				parameters.Add(new SqlParameter("@p4", SqlDbType.Int, 4, YEAR));
				parameters.Add(new SqlParameter("@p5", SqlDbType.VarChar, 15, BUILDING));
				parameters.Add(new SqlParameter("@p6", SqlDbType.VarChar, 7, ROOM_NUMBER));
				parameters.Add(new SqlParameter("@p7", SqlDbType.VarChar, 4, TIME_SLOT_ID));
				string query = string.Format("INSERT INTO section({0}, {1}, {2}, {3}, {4}, {5}, {6}) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)", COURSE_ID, SECTION_ID, SEMESTER, YEAR, BUILDING, ROOM_NUMBER, TIME_SLOT_ID);
				DataServices.ExecuteQuery(QueryType.INSERT, CommandType.Text, query, parameters, this);
				return true;
			}
			catch (ConstraintException ex)
			{
				this.RejectChanges();
				System.Windows.Forms.MessageBox.Show(ex.Message);
				return false;
			}
			catch (SqlException ex)
			{
				this.RejectChanges();
				System.Windows.Forms.MessageBox.Show(ex.Message);
				return false;
			}
			catch (System.Exception ex)
			{
				System.Windows.Forms.MessageBox.Show(ex.ToString());
				return false;
			}
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="section"></param>
		public bool Update(Business.Section section)
		{
			try
			{
				string query = "UPDATE section SET course_id=@a1, sec_id=@a2, semester=@a3, year=@a4, building=@a5, room_number=@a6, time_slot_id=@a7 WHERE course_id=@p1 AND sec_id=@p2 AND semester=@p3 AND year=@p4";
				List<SqlParameter> parameters = new List<SqlParameter>();
				parameters.Add(new SqlParameter("@a1", SqlDbType.VarChar, 8, COURSE_ID));
				parameters.Add(new SqlParameter("@a2", SqlDbType.VarChar, 8, SECTION_ID));
				parameters.Add(new SqlParameter("@a3", SqlDbType.VarChar, 6, SEMESTER));
				parameters.Add(new SqlParameter("@a4", SqlDbType.Int, 4, YEAR));
				parameters.Add(new SqlParameter("@a5", SqlDbType.VarChar, 15, BUILDING));
				parameters.Add(new SqlParameter("@a6", SqlDbType.VarChar, 7, ROOM_NUMBER));
				parameters.Add(new SqlParameter("@a7", SqlDbType.VarChar, 4, TIME_SLOT_ID));
				var p1 = new SqlParameter("@p1", SqlDbType.VarChar, 8, COURSE_ID);
				p1.SourceVersion = DataRowVersion.Original;
				parameters.Add(p1);
				var p2 = new SqlParameter("@p2", SqlDbType.VarChar, 8, SECTION_ID);
				p2.SourceVersion = DataRowVersion.Original;
				parameters.Add(p2);
				var p3 = new SqlParameter("@p3", SqlDbType.VarChar, 6, SEMESTER);
				p3.SourceVersion = DataRowVersion.Original;
				parameters.Add(p3);
				var p4 = new SqlParameter("@p4", SqlDbType.Int, 4, YEAR);
				p4.SourceVersion = DataRowVersion.Original;
				parameters.Add(p4);
				DataServices.ExecuteQuery(QueryType.UPDATE, CommandType.Text, query, parameters, this);
				return true;
			}
			catch (ConstraintException ex)
			{
				this.RejectChanges();
				System.Windows.Forms.MessageBox.Show(ex.Message);
				return false;
			}
			catch (SqlException ex)
			{
				this.RejectChanges();
				System.Windows.Forms.MessageBox.Show(ex.Message);
				return false;
			}
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="section"></param>
		public bool Delete(Business.Section section)
		{
            try
            {
                (section as DataRow).Delete();
                String query = String.Format("DELETE FROM section WHERE {0}=@p0 AND {1}=@p1 AND {2}=@p2 AND {3}=@p3", COURSE_ID, SECTION_ID, SEMESTER, YEAR);
                List<SqlParameter> parameters = new List<SqlParameter>();
                parameters.Add(new SqlParameter("@p0", SqlDbType.VarChar, 8, COURSE_ID));
                parameters.Add(new SqlParameter("@p1", SqlDbType.VarChar, 8, SECTION_ID));
                parameters.Add(new SqlParameter("@p2", SqlDbType.VarChar, 6, SEMESTER));
                parameters.Add(new SqlParameter("@p3", SqlDbType.Int, 4, YEAR));
                DataServices.ExecuteQuery(QueryType.DELETE, CommandType.Text, query, parameters, this);
				return true;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.ToString());
				return false;
            }
		}
		public void Search(string courseID)
		{
			
		}
	}
}
